package com.server.userCenter;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author dell
 *
 */
@Mapper
public interface UserCenterDAO {

	/**
	 * 乐观锁扣减金额
	 * @return
	 */
	@Update("update user u set u.amount_money = u.amount_money - #{amount} where u.amount_money > #{amount} and u.id= #{userid}")
	public int deuUserAmount(@Param("userid")String userid,@Param("amount")double amount);
	
	/**
	 * 查询所有用户
	 * @param userid
	 * @return
	 */
	@Select("select * from user where status = '1' ")
	public List<Map<String, Object>> getAllUser();
	/**
	 * 查询用户
	 * @param userid
	 * @return
	 */
	@Select("select * from user where id=#{userid}")
	public Map<String, Object> getUserById(@Param("userid")String userid);
	
	/**
	 * 查询用户地址列表
	 * @return
	 */
	@Select("select * from addr where status = '1' and  userid=#{userid} order by isdefault desc , ctime desc ")
	public List<Map<String, Object>> getUserAddr(@Param("userid")String userid);

	/**
	 * 新增收获地址
	 * @param id
	 * @param userid
	 * @param province
	 * @param city 
	 * @param area
	 * @param addr
	 * @param phone
	 * @param name
	 * @return
	 */
	@Insert("insert into addr (id,userid,province,city,area,addr,phone,name,isdefault)values(#{id},#{userid},#{province},#{city},#{area},#{addr},#{phone},#{name},#{isdefault})")
	public int addAddr(@Param("id")String id,@Param("userid")String userid,@Param("province")String province,@Param("city")String city,@Param("area")String area,
			@Param("addr")String addr,@Param("phone")String phone,@Param("name")String name,@Param("isdefault")String isdefault);
	
	/**
	 * 修改收获地址
	 * @param map
	 * @return
	 */
	public int upAddr(Map<String, Object> map);
	
	/**
	 * 设置默认地址
	 * @param id
	 * @param userid
	 * @return
	 */
	@Update("update addr set isdefault = '0' where userid=#{userid} ; update addr set isdefault = '1' where userid=#{userid} and id=#{id} ")
	public int setDefaultAddr(@Param("id")String id,@Param("userid")String userid);
	
	/**
	 * 获取用户默认地址
	 * @param userid
	 * @return
	 */
	@Select("select * from addr ad where ad.userid=#{userid} and ad.status='1' order by ad.isdefault desc , ctime desc limit 1")
	public Map<String, Object> getDefaultAddr(@Param("userid")String userid);
	
	/**
	 * 查询订单列表
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> getList(Map<String, Object> map);
	
	/**
	 * 查询订单详情
	 * @param id
	 * @return
	 */
	@Select("select l.*,ad.addr,ad.phone,ad.`name`,ad.province,ad.city,ad.area from shoplist l left join addr ad on l.addrid=ad.id where l.id= #{id}")
	public Map<String, Object> getListDetail(@Param("id")String id);
	
	/**
	 * 更新订单详情
	 * @param map
	 * @return
	 */
	public int upList(Map<String, Object> map);
	
	/**
	 * 查询某订单得商品信息
	 * @param id
	 * @return
	 */
	@Select("select * from list_goods_view lgv where  lgv.listid=#{id}")
	public List<Map<String, Object>> getListGoods(@Param("id")String id);
	

	/**
	 * 批量扣减商品库存
	 * @param list
	 */
	public void deuGoodsStock(@Param("data")List<Map<String, Object>> data);

	/**
	 * 将库存小于0的商品库存变成0，防止出现负数
	 */
	@Update("update stock set stock = 0 where stock <0")
	public void upStock2zero();
	
	
	/**
	 * 更新用户金额
	 * @param userid
	 * @param amount_money
	 * @param amount_ad
	 * @param amount_lq
	 * @return
	 */
	@Update("update user u set u.amount_money=u.amount_money-0+#{amount_money} , u.amount_ad=u.amount_ad-0+#{amount_ad}, u.amount_lq=u.amount_lq-0+#{amount_lq}  where u.id=#{userid}")
	public int upUserAmount(@Param("userid")String userid,@Param("amount_money")double amount_money,@Param("amount_ad")double amount_ad,@Param("amount_lq")double amount_lq);
	

	/**
	 * 查询用户累计消费礼券
	 * @param userid
	 * @return
	 */
	@Select("select ifnull(sum(l.lq),0) s from lqlog l where l.type= '2' and l.userid=#{userid} ")
	public Map<String, Object> countLQ(@Param("userid")String userid);
	
	/**
	 * 根据条件查询礼券记录
	 * @param data
	 * @return
	 */
	public List<Map<String, Object>> getLQLog(Map<String, Object> data);
	
	/**
	 * 查询艾豆记录
	 * @param data
	 * @return
	 */
	public List<Map<String, Object>> getADLog(Map<String, Object> data);
	
	/**
	 * 批量插入艾豆记录
	 * @param list #{list.id},#{list.type},#{list.ad},#{list.month},#{list.operation}
	 * @return
	 */
	public int insAdLogList(@Param("list")List<Map<String, Object>> list);
	
	/**
	 * 批量更新艾豆
	 * @param list #{list.id} #{list.ad} 
	 * @return
	 */
	public int upUserADList(@Param("list")List<Map<String, Object>> list);
	
	/**
	 * 插入礼券交易记录
	 * @param id
	 * @param userid
	 * @param type
	 * @param lq
	 * @param operation
	 * @param des
	 * @param listid
	 * @return
	 */
	@Update("insert into lqlog (id,userid,type,lq,lq_now,operation,des,listid)values(#{id},#{userid},#{type},#{lq},(select amount_lq from user where id=#{userid}),#{operation},#{des},#{listid})")
	public int insLQLog(@Param("id")String id,@Param("userid")String userid,@Param("type")String type,@Param("lq")String lq,@Param("operation")String operation,@Param("des")String des,@Param("listid")String listid);
	
	/**
	 * 查询金额记录
	 * @param userid
	 * @param type
	 * @param operation
	 * @param stime
	 * @param etime
	 * @return
	 */
	public List<Map<String, Object>> getAmountLog(@Param("userid")String userid,@Param("type")String type,@Param("operation")String operation,@Param("stime")String stime,@Param("etime")String etime);
	
	/**
	 * 更新用户积分
	 * @param userid
	 * @param amount_tx
	 * @param amount_gw
	 * @return
	 */
	@Update("update user u set u.point=u.point-0+#{point} where u.id=#{userid}")
	public int upUserPoint(@Param("userid")String userid,@Param("point")int point);
	
	/**
	 * 插入交易记录
	 * @param id
	 * @param userid
	 * @param type '1:收入，2：支出',
	 * @param amount
	 * @param operation '交易类型1：购买商品',
	 * @return
	 */
	@Insert("insert into amountlog (id,userid,type,amount,operation,des,listid,amount_now)values(#{id},#{userid},#{type},#{amount},#{operation},#{des},#{listid},(select amount_tx from user where id=#{userid}))")
	public int insAmountLog(@Param("id")String id,@Param("userid")String userid,@Param("type")String type,@Param("amount")double amount,@Param("operation")String operation,@Param("des")String des,@Param("listid")String listid);

	
	/**
	 * 插入积分记录
	 * @param id
	 * @param userid
	 * @param type '1:收入，2：支出',
	 * @param amount
	 * @param operation '交易类型1：购买商品',
	 * @return
	 */
	@Insert("insert into pointlog (id,userid,type,point,operation,des,listid,point_now) values (#{id},#{userid},#{type},#{point},#{operation},#{des},#{listid},(select point from user where id=#{userid}))")
	public int insPointLog(@Param("id")String id,@Param("userid")String userid,@Param("type")String type,@Param("point")int point,@Param("operation")String operation,@Param("des")String des,@Param("listid")String listid);

	
	/**
	 * 查询所以上级用户
	 * @param tjcodein 推荐人编号合计，样例：('1','2')
	 * @return
	 */
	@Select("select * from user u where u.tjcode in ${tjcodein} order by u.generation desc")
	public List<Map<String, Object>> getUpLevelUser(@Param("tjcodein")String tjcodein);
	
	/**
	 * 查询直属VIP数量
	 * @param id
	 * @return
	 */
	@Select("select count(1)c from user u WHERE u.tjrcode=#{tjcode} and u.`level`>1")
	public Map<String, Object> countDirectUser(@Param("tjcode")String tjcode);
	
	/**
	 * 查询直属人员列表
	 * @param id
	 * @return
	 */
	@Select("select u.*,d.codeval levelStr from user u left join dict d on u.level = d.id  where u.tjrcode=#{tjcode}")
	public List<Map<String, Object>> getDirectUser(@Param("tjcode")String tjcode);
	
	/**
	 * 查询团队销售数量
	 * @param id
	 * @return
	 */
	@Select("select ifnull(sum(lg.reward_amount/599),0) su from shoplist lg where lg.status in ('2','3','4') and (lg.ifreward = '1' or lg.ifreward is null ) and ( userid in "
			+ " (select u.id from user u where u.tjtree like concat (#{tjtree},'%') )|| userid=#{userid} )")
	public Map<String, Object> countTeamSell(@Param("tjtree")String tjtree,@Param("userid")String userid);
	
//	/**
//	 * 查询小部门销售数量
//	 * @param id
//	 * @return
//	 */
//	@Select("select ifnull(sum(lg.amount/599),0) su from list_goods_view lg where lg.status in ('2','3','4') and userid in (select id from user u where u.tjtree like concat (u.tjtree,'%')) and userid <>(select x.id from "
//			+ " (select u.*,(select ifnull(sum(lg.num),0) su from list_goods_view lg where lg.status in ('2','3','4') and userid in (select id from user u where u.tjtree like concat (u.tjtree,'%')))s "
//			+ " from user u where u.tjrcode=#{tjcode}  order by s desc  limit 1)x )")
//	public Map<String, Object> countSdeptSell(@Param("tjtree")String tjtree,@Param("tjcode")String tjcode);
	
	/**
	 * 查询用户有几个对应级别的人
	 * @param generation
	 * @param tjcode
	 * @return
	 */
	@Select("select count(1) c from (select u.*,(select count(1) from user u1 where u1.level=#{level} and u1.tjrcode=u.tjcode )c from user u where u.tjrcode=#{tjcode})uu where uu.c >0")
	public Map<String, Object> countLevelUser(@Param("level")String level,@Param("tjcode")String tjcode);
	
//	/**
//	 * 查询当前用户历史订单中，购买商品最多的数量
//	 * @param userid
//	 * @return
//	 */
//	@Select("select IFNULL(MAX(num),0)max from list_goods_view l where l.userid=#{userid} and l.`status` in ('2','3','4') ")
//	public Map<String, Object> getMaxNum(@Param("userid")String userid);
	
	/**
	 * 查询当前用户历史订单中，购买商品最多的数量——改成查询599的倍数
	 * @param userid
	 * @return
	 */
	@Select("select  IFNULL(max(reward_amount)/599,0) maxNum from list l where l.userid=#{userid} and l.`status` in ('2','3','4') ")
	public Map<String, Object> getMaxNum(@Param("userid")String userid);
	
	/**
	 * 更新用户级别
	 * @param userid
	 * @param level
	 * @return
	 */
	@Update("update user set level = #{level} where id= #{userid}")
	public int upUserLevel(@Param("userid")String userid,@Param("level")String level);
	
	/**
	 * 批量插入amountlog表
	 * @param list #{list.id} ,#{list.type} ,#{list.log_amount} ,#{list.operation} 
	 */
	public void insAmountLogList(@Param("list")List<Map<String, Object>> list);
	
	/**
	 * 批量更新用户金额
	 * @param list #{list.log_amount}   #{list.id}
	 */
	public void upAmountList(@Param("list")List<Map<String, Object>> list);
	
	/**
	 * 根据推荐吗/手机号查询用户
	 * @param serchStr
	 * @return
	 */
	@Select("select * from user where tjcode =#{serchStr} or phone =#{serchStr}")
	public List<Map<String, Object>> getUserByCP(@Param("serchStr")String serchStr);
	
	/**
	 * 转账
	 * @param userid
	 * @param targetCode
	 * @param amount
	 */
	@Update("update user u set u.amount_tx=u.amount_tx-#{amount} where u.id=#{userid};"
			+ " update user u set u.amount_tx=u.amount_tx+#{amount} where u.id=#{reciveId} ")
	public void doTransfer(@Param("userid")String userid,@Param("reciveId")String reciveId,@Param("amount")String amount);
	
	/**
	 * 更新密码
	 * @param userid
	 * @param oldPwd
	 * @param newPwd
	 * @return
	 */
	@Update("update user u set u.pwd=#{newPwd} where u.id=#{userid} and u.pwd=#{oldPwd} ")
	public int updatePwd(@Param("userid")String userid,@Param("oldPwd")String oldPwd,@Param("newPwd")String newPwd);
	
	/**
	 * 更新支付密码
	 * @param userid
	 * @param oldPwd
	 * @param newPwd
	 * @return
	 */
	@Update("update user u set u.pay_pwd=#{newPwd} where u.id=#{userid} and u.pay_pwd=#{oldPwd} ")
	public int updatePwd_pay(@Param("userid")String userid,@Param("oldPwd")String oldPwd,@Param("newPwd")String newPwd);
	/**
	 * 更新用户信息
	 * @param map
	 * @return
	 */
	public int updateUser(Map<String, Object> map);
	
	/**
	 * 插入提现记录
	 * @param id
	 * @param userid
	 * @param bank
	 * @param cardcode
	 * @param username
	 * @param amount
	 * @return
	 */
	@Insert("insert into cashlog (userid,type,day,amount,status,des,bid,sendid)values(#{userid},#{type},#{day},#{amount},#{status},#{des},#{bid},#{sendid})")
	public int insCashLog(@Param("userid")String userid,@Param("type")String type,@Param("day")String day,@Param("amount")String amount,@Param("status")String status,@Param("des")String des,@Param("bid")String bid,@Param("sendid")String sendid);
	
	/**
	 * 查询用户提现金额
	 * @param userid
	 * @return
	 */
	@Select("select ifnull(sum(c.amount),0) s from cashlog c where c.userid=#{userid} and c.`status`='2'")
	public Map<String, Object> countCashLog(@Param("userid")String userid);
	

	/**
	 * 查询提现记录
	 * @param userid
	 * @return
	 */
	@Select("select * from cashlog cl where cl.userid=#{userid} order by cl.ctime desc  ")
	public List<Map<String, Object>> getCashLog(@Param("userid")String userid);
	
	/**
	 * 查询用户当天提现几次
	 * @param userid
	 * @param day
	 * @return
	 */
	@Select("select count(1) c from cashlog c where c.userid=#{userid} and c.`day`=#{day} and c.type=#{type} ")
	public Map<String, Object> countCashDayLog(@Param("userid")String userid,@Param("day")String day,@Param("type")String type);
	
	
	/**
	 * 更新全部商品已售数量
	 */
	@Update("update goods g set g.soldnum = ( select ifnull(sum(num),0) from shoplist_goods lg left join shoplist l on lg.listid=l.id where lg.goodsid=g.id and l.status in ('2','3','4') )")
	public void upGoodsSoldNum();
	
	/**
	 * 查询指定订单，指定用户，指定类型的金额记录
	 * @param listid
	 * @param userid
	 * @param operation
	 * @return
	 */
	@Select("select * from amountlog a where a.listid=#{listid} and a.userid=#{userid} and a.operation=#{operation} ")
	public Map<String, Object> getListMoneyDetail(@Param("listid")String listid,@Param("userid")String userid,@Param("operation")String operation);
	
	/**
	 * 查询加盟详情
	 * @param id
	 * @return
	 */
	@Select("select s.*,g.headpic from shop_join s left join goods g on s.goodsid = g.id where userid = #{userid}")
	public List<Map<String, Object>> getShopJoinList(@Param("userid")String userid);
	
	/**
	 * 查询加盟详情
	 * @param id
	 * @return
	 */
	@Select("select s.*,g.price from shop_join s left join goods g on s.goodsid = g.id where s.id = #{id}")
	public Map<String, Object> getShopJoin(@Param("id")int id);
	
	/**
	 * 更新加盟订单记录
	 * @param id
	 * @param status
	 * @return
	 */
	@Update("update shop_join s set s.status=#{status} where s.id=#{id}")
	public int upJoinLog(@Param("id")int id,@Param("status")String status);
	
	/**
	 * 删除记录
	 * @param id
	 * @return
	 */
	@Delete("delete from shop_join where id=#{id} and status='0'")
	public int delJoinLog(@Param("id")int id);
}
